// 数据库初始化脚本
require('dotenv').config();
const mysql = require('mysql2/promise');
const fs = require('fs');
const path = require('path');

// 数据库连接配置
const dbConfig = {
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 3306,
  user: process.env.DB_USER || 'root1',
  password: process.env.DB_PASSWORD || 'ddydtx1126',
  charset: 'utf8mb4',
  timezone: '+08:00',
  multipleStatements: true
};

// 执行SQL文件的辅助函数
async function executeSqlFile(connection, filePath) {
  try {
    console.log(`📄 正在执行SQL文件: ${filePath}`);
    const sql = fs.readFileSync(filePath, 'utf8');
    
    // 分割SQL语句并执行
    const statements = sql
      .split('\n')
      .filter(line => line.trim() && !line.trim().startsWith('--'))
      .join('\n')
      .split(';')
      .map(stmt => stmt.trim())
      .filter(stmt => stmt.length > 0);

    for (const statement of statements) {
      if (statement.trim()) {
        await connection.query(statement);
      }
    }
    
    console.log(`✅ SQL文件执行完成: ${filePath}`);
  } catch (error) {
    console.error(`❌ 执行SQL文件失败: ${filePath}`, error.message);
    throw error;
  }
}

async function initializeDatabase() {
  let connection = null;
  
  try {
    console.log('🚀 开始初始化数据库...');
    console.log('🔄 正在连接MySQL数据库...');
    
    // 连接到MySQL服务器
    connection = await mysql.createConnection(dbConfig);
    console.log('✅ MySQL连接成功');

    // 删除现有数据库
    console.log('🗑️ 删除现有数据库...');
    await connection.query('DROP DATABASE IF EXISTS ballsystem');

    // 执行初始化SQL
    const initSqlPath = path.join(__dirname, '../database/init.sql');
    await executeSqlFile(connection, initSqlPath);

    // 执行种子数据SQL
    const seedSqlPath = path.join(__dirname, '../database/seed.sql');
    await executeSqlFile(connection, seedSqlPath);

    console.log('🎉 数据库初始化完成！');
    console.log('📊 验证数据...');
    
    // 验证数据
    await connection.query('USE ballsystem');
    
    const [tables] = await connection.query('SHOW TABLES');
    console.log('📋 数据库表:', tables.map(t => Object.values(t)[0]));
    
    const [userCount] = await connection.query('SELECT COUNT(*) as count FROM users');
    console.log('👥 用户数量:', userCount[0].count);
    
    const [tableCount] = await connection.query('SELECT COUNT(*) as count FROM tables');
    console.log('🎱 台球桌数量:', tableCount[0].count);
    
    const [orderCount] = await connection.query('SELECT COUNT(*) as count FROM orders');
    console.log('📋 订单数量:', orderCount[0].count);

    const [inventoryCount] = await connection.query('SELECT COUNT(*) as count FROM inventory');
    console.log('📦 库存项目数量:', inventoryCount[0].count);
    
    console.log('✨ 数据库初始化成功！现在可以启动服务器了。');
    
  } catch (error) {
    console.error('❌ 数据库初始化失败:', error.message);
    console.error('详细错误:', error);
    process.exit(1);
  } finally {
    if (connection) {
      await connection.end();
    }
  }
}

// 检查命令行参数
const args = process.argv.slice(2);
const forceReset = args.includes('--force') || args.includes('-f');

if (forceReset) {
  console.log('⚠️ 强制重置模式：将删除现有数据库并重新创建');
  initializeDatabase();
} else {
  // 询问用户确认
  const readline = require('readline');
  const rl = readline.createInterface({
    input: process.stdin,
    output: process.stdout
  });

  rl.question('⚠️ 此操作将重置数据库并删除所有现有数据，是否继续？(y/N): ', (answer) => {
    if (answer.toLowerCase() === 'y' || answer.toLowerCase() === 'yes') {
      initializeDatabase();
    } else {
      console.log('❌ 操作已取消');
      process.exit(0);
    }
    rl.close();
  });
}

module.exports = { initializeDatabase };
